Spatial SQL

Introduction

One of the advantages of using a spatially-enabled database is that you execute "spatial" SQL commands. In other words, you can put spatial operations into your SQL statements to help filter the data.

Geometry Types

When you imported a shapefile into PostgreSQL, you may have noticed that the type for the column was "geometry". This is a general column type that can include different types of spatial data and you can even mix the types. The types are defined by the OpenGIS standards group and are shown below.

You don't need to use these types if you only interact with spatial data from applications like QGIS. However, if you need to manage the database directly or want to explore using these data types in other applications, like the web, you'll need to be familiar with them. The main ones you'll use are:

• POINT - for individual points
• MULTILINESTRING - this is the same as a polyline and can contain multiple sequences of points to represent a stream or road network.
• MULTIPOLYGON - this is the same as a "polygon" in a shapefile and can contain multiple polygons. Polygons always have an "Exterior" polygon and may contain "Interior" polygons (i.e. lakes in a states layer).
• GEOMETRYCOLLECTION - this is a collection of different geometries.

POLYGON - don't confuse this with a "polygon" in a shapefile. This is a real polygon and has only one ring of points.

GEOMETRYCOLLECTION - while collections can contain different types of geometries (i.e. a point and a line string as shown above). This is not recommended as it makes handling the data much more complicated and is not compatible with Shapefiles.

WKT and WKB

PostGIS uses the "Well Known Text" (WKT) and "Well Known Binary" (WKB) data formats to encode spatial data. Below are examples of WKT versions of some of the geometries.

The coordinate values are ordered (x,y) with no spaces. The individual coordinates are then separated by commas. Individual elements within the geometries are then grouped with parenthesis. Note that polygons must repeat their starting coordinate at the end of the polygon.

EWKT and EWKB – Extended Well-Known Text/Binary – A PostGIS-specific format that includes the spatial reference system identifier (SRID) and up to 4 ordinate values.[3][4] For example: SRID=4326;POINT(-44.3 60.1) to locate a longitude/latitude coordinate using the WGS 84 reference coordinate system (Wikipedia).

PostGIS SQL Commands

PostGIS contains a large number of commands that are especially for accessing and managing spatial data. For example, the following command will print out the cities geometries as text:

SELECT ST_AsText(geom)
FROM cities2sp

Below is a more advanced spatial SQL statement to select the cities that fall completely within a bounding polygon.

SELECT ST_AsText(geom)
FROM cities2sp
WHERE ST_Within(geom,POLYGON((0 0,1300000 0,1300000 500000,0 500000,0 0))

Below is a summary of commonly used commands. This does not include the commands to create tables and define SRIDs. See the PostGIS reference for these.

ST_Distance(geometry A, geometry B)	Distance between the two geometries
ST_DWithin(geometry A, geometry, float Distance)	TRUE if the two geometries are within the specified distance
ST_Intersects(geometry A, geometry B)	TRUE if the geometries intersect with one another
ST_Envelope(geometry A)	Returns a bounding box for the geometry
ST_Intersects(geometry A, geometry B)	TRUE if A intersects with B
ST_Touches(geometry A, geometry B)	TRUE if A touches B
ST_Within(geometry A, geometry B)	TRUE if A is completely inside B
ST_Area(geometry A)	Rweturns area for polygons
ST_Length(geometry A)	Returns total length of line segments
ST_Intersection(geometry A, geometry B)	Returns a geometry with the intersection of A and B
ST_Union(geometry A, geometry B)	Returns a geometry with the union of A and B
ST_AsText(geometry A)	returns the WKT form of the geometry
ST_IsEmpty(geometry A)	Returns TRUE if there are no points in this geometry
ST_NumGeometries(geometry A)	returns the number of geometries within the geometry collection
ST_GeometryN(geometry A,int Index)	Returns the nth geometry in the collection
GeometryType(geometry A)	Returns a string with the geometry type('LINESTRING', 'POLYGON', 'MULTIPOINT')
ST_GeomFromText(text,[<srid>])	Creates a geometry from WKT with specified SRID
ST_AsText(geometry A)	Returns the geometry as text
ST_MakeEnvelope(XMin,YMin,XMax,YMax,SRID)	Creates a rectangular polygon from the values provided.

More Resources

PostGIS Reference. Chapter 4. Using PostGIS

PostGIS Reference. Chapter 6. PostGIS Reference